﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using WCF_BI_Portal.GeneralLibs;
using WCF_BI_Portal.Lists;
using WCF_BI_Portal.Controller;
using System.Xml;
using WCF_BI_Portal.CustomSecurity;
using WCF_BI_Portal.Lists;

namespace WCF_BI_Portal
{
    // NOTE: If you change the class name "Service1" here, you must also update the reference to "Service1" in Web.config and in the associated .svc file.
    public class BIPortal : IBIPortal
    {
        string IBIPortal.ThongTinTongTheOut(string dbName, DateTime thoiDiemDau, DateTime thoiDiemCuoi, bool tinhCongNo)
        {
            ReUseLibs.ResetConnection(dbName);
            SqlConnection con = new SqlConnection(LocalSettings.ConnectString);

            try
            {
                //Kiểm tra xem nếu xem từ đầu kỳ cuối thì phải tính cả tồn đầu kỳ còn các trường hợp khác thì chỉ tính phát sinh.
                bool xemTuDauKyCuoi = false;
                ThoiDiemKhoaSo thoiDiemKhoaSoGanNhat = DanhMucThoiDiemKhoaSo.Instance.GetThoiDiemKhoaSoGanNhat(DateTime.Now);
                if (thoiDiemKhoaSoGanNhat.ThoiDiem == thoiDiemDau)
                    xemTuDauKyCuoi = true;

                //5 Ngày reindex 1 lần
                if (Config.ThoiDiemReIndexGanNhat is NullableDate)
                {
                    TimeSpan span = DateTime.Now - (DateTime)Config.ThoiDiemReIndexGanNhat.Date;
                    if (span.TotalDays > 5) ReUseLibs.ReIndexDB();
                }
                else
                {
                    ReUseLibs.ReIndexDB();
                    Config.ThoiDiemReIndexGanNhat = new NullableDate(DateTime.Now);
                }


                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                cmd.ExecuteNonQuery();

                Controller.DsWCFBI ds = new Controller.DsWCFBI();
                DataTable dtThongTinTongThe = ds.Tables["ThongTinTongThe"];
                DateTime dtPointer = thoiDiemDau;
                Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                Guid taiKhoanTienMatID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("TienMat");
                SoDuTaiKhoan soDuTaiKhoanTienMat = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanTienMatID);

                DataRow row1 = dtThongTinTongThe.NewRow();
                row1["Key"] = "TM";
                row1["Value"] = soDuTaiKhoanTienMat.DuNo;
                dtThongTinTongThe.Rows.Add(row1);

                Guid taiKhoanTienGuiNganHangID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("112").ID;
                SoDuTaiKhoan soDuTaiKhoanTienGuiNganHang = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanTienGuiNganHangID);

                DataRow row2 = dtThongTinTongThe.NewRow();
                row2["Key"] = "TGNH";
                row2["Value"] = soDuTaiKhoanTienGuiNganHang.DuNo;
                dtThongTinTongThe.Rows.Add(row2);

                //Guid taiKhoanHangHoaID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("GiaMuaHangHoa");
                //SoDuTaiKhoan soDuTaiKhoanHangHoa = SoDuTaiKhoan.CalculateFromDB(thoiDiemCuoi, taiKhoanHangHoaID);
                decimal giaVonHangHoa = ReUseLibs.CalculateGiaVonUocTinhConTrongKho(thoiDiemCuoi);
                if (giaVonHangHoa < 0)
                {
                    //var error = new CustomFaultMsg { Message = "Cần kiểm tra lại số dư tài khoản hàng hóa. Hiện tại đang âm " + soDuTaiKhoanHangHoa.DuCo.ToString() };
                    throw new FaultException("Cần kiểm tra lại số dư tài khoản hàng hóa. Hiện tại đang âm ");
                }

                DataRow row3 = dtThongTinTongThe.NewRow();
                row3["Key"] = "GVHH";
                row3["Value"] = giaVonHangHoa;
                dtThongTinTongThe.Rows.Add(row3);

                //Phải thu phải trả sau bù trừ
                if (tinhCongNo)
                {
                    List<decimal> phaiThuPhaiTraSauBuTru = ReUseLibs.CalculatorCongNoSauBuTru(cmd, thoiDiemCuoi);

                    DataRow row4 = dtThongTinTongThe.NewRow();
                    row4["Key"] = "CNPThu";
                    row4["Value"] = phaiThuPhaiTraSauBuTru[0];
                    dtThongTinTongThe.Rows.Add(row4);

                    DataRow row5 = dtThongTinTongThe.NewRow();
                    row5["Key"] = "CNPTra";
                    row5["Value"] = phaiThuPhaiTraSauBuTru[1];
                    dtThongTinTongThe.Rows.Add(row5);
                }

                Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");
                decimal tongThu = NhatKyThuChi.TongThu(cmd, thoiDiemDau, thoiDiemCuoi);
                decimal tongChi = NhatKyThuChi.TongChi(cmd, thoiDiemDau, thoiDiemCuoi);
                DataRow row6 = dtThongTinTongThe.NewRow();
                row6["Key"] = "TT";
                row6["Value"] = tongThu;
                dtThongTinTongThe.Rows.Add(row6);

                DataRow row7 = dtThongTinTongThe.NewRow();
                row7["Key"] = "TC";
                row7["Value"] = tongChi;
                dtThongTinTongThe.Rows.Add(row7);

                //Chia thu làm 3 loại
                //Nguồn vốn 411
                TaiKhoan tk411 = DanhMucTaiKhoan.Instance.GetBySoHieu("411");
                SoDuTaiKhoan soDu411 = SoDuTaiKhoan.CalculatePhatSinhTrongKy_KhongBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk411.ID);
                decimal phatSinhThu411 = soDu411.DuCo;  //Có là phát sinh thu

                DataRow row8 = dtThongTinTongThe.NewRow();
                row8["Key"] = "TNV";
                row8["Value"] = phatSinhThu411;
                dtThongTinTongThe.Rows.Add(row8);

                //Thu khác 711
                decimal thuKhac = 0;
                TaiKhoan tk711 = DanhMucTaiKhoan.Instance.GetBySoHieu("711");
                TaiKhoan tk515 = DanhMucTaiKhoan.Instance.GetBySoHieu("515");
                SoDuTaiKhoan soDu711 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk711.ID);
                SoDuTaiKhoan soDu515 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk515.ID);
                decimal phatSinh711 = Math.Abs(soDu711.DuNo - soDu711.DuCo);
                decimal phatSinh515 = Math.Abs(soDu515.DuNo - soDu515.DuCo);
                thuKhac = phatSinh711 + phatSinh515;
                DataRow row9 = dtThongTinTongThe.NewRow();
                row9["Key"] = "TK";
                row9["Value"] = thuKhac;
                dtThongTinTongThe.Rows.Add(row9);

                //Thu bán hàng: các thu còn lại
                decimal tongThuBanHang = NhatKyThuChi.TongThuBanHang(cmd, thoiDiemDau, thoiDiemCuoi);
                DataRow row10 = dtThongTinTongThe.NewRow();
                row10["Key"] = "TBH";
                row10["Value"] = tongThuBanHang;
                dtThongTinTongThe.Rows.Add(row10);

                //Chi chia làm 5 loại.
                //Chi khác 811, 635
                decimal chiPhiKhac = 0;
                TaiKhoan tk811 = DanhMucTaiKhoan.Instance.GetBySoHieu("811");
                TaiKhoan tk635 = DanhMucTaiKhoan.Instance.GetBySoHieu("635");
                SoDuTaiKhoan soDu811 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk811.ID);
                SoDuTaiKhoan soDu635 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk635.ID);
                decimal phatSinh811 = Math.Abs(soDu811.DuCo - soDu811.DuNo);
                decimal phatSinh635 = Math.Abs(soDu635.DuCo - soDu635.DuNo);
                chiPhiKhac = phatSinh811 + phatSinh635;
                DataRow row11 = dtThongTinTongThe.NewRow();
                row11["Key"] = "CK";
                row11["Value"] = chiPhiKhac;
                dtThongTinTongThe.Rows.Add(row11);

                //Chi phí hoạt động và sản xuất 641 . 642
                decimal chiPhiHoatDong = 0;
                TaiKhoan tk641 = DanhMucTaiKhoan.Instance.GetBySoHieu("641");
                TaiKhoan tk642 = DanhMucTaiKhoan.Instance.GetBySoHieu("642");
                TaiKhoan tk334 = DanhMucTaiKhoan.Instance.GetBySoHieu("334");
                TaiKhoan tk3383 = DanhMucTaiKhoan.Instance.GetBySoHieu("3383");
                TaiKhoan tk3384 = DanhMucTaiKhoan.Instance.GetBySoHieu("3384");

                SoDuTaiKhoan soDu641 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk641.ID);
                SoDuTaiKhoan soDu642 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk642.ID);
                SoDuTaiKhoan soDu334 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk334.ID);
                SoDuTaiKhoan soDu3383 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk3383.ID);
                SoDuTaiKhoan soDu3384 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk3384.ID);

                decimal phatSinh641 = Math.Abs(soDu641.DuNo - soDu641.DuCo);
                decimal phatSinh642 = Math.Abs(soDu642.DuNo - soDu642.DuCo);
                decimal phatSinh334 = Math.Abs(soDu334.DuNo - soDu334.DuCo);
                decimal phatSinh3383 = Math.Abs(soDu3383.DuNo - soDu3383.DuCo);
                decimal phatSinh3384 = Math.Abs(soDu3384.DuNo - soDu3384.DuCo);

                DataRow row12 = dtThongTinTongThe.NewRow();
                row12["Key"] = "CPHD";
                row12["Value"] = chiPhiHoatDong;
                dtThongTinTongThe.Rows.Add(row12);

                //Chi phí sản xuất
                Guid taiKhoanChiPhiSXID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("213").ID;
                SoDuTaiKhoan soDu213 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, taiKhoanChiPhiSXID);
                decimal chiPhiSX = Math.Abs(soDu213.DuNo - soDu213.DuCo);
                DataRow row13 = dtThongTinTongThe.NewRow();
                row13["Key"] = "CPSX";
                row13["Value"] = chiPhiSX;
                dtThongTinTongThe.Rows.Add(row13);

                //Chi nguồn vốn 411
                //Nguồn vốn 411
                decimal phatSinhChi411 = soDu411.DuNo;
                DataRow row14 = dtThongTinTongThe.NewRow();
                row14["Key"] = "CTNV";
                row14["Value"] = phatSinhChi411;
                dtThongTinTongThe.Rows.Add(row14);

                //chi phí mua hàng
                decimal tongChiPhiMuaHang = NhatKyThuChi.TongChiMuaHang(cmd, thoiDiemDau, thoiDiemCuoi);
                DataRow row15 = dtThongTinTongThe.NewRow();
                row15["Key"] = "CMH";
                row15["Value"] = tongChiPhiMuaHang;
                dtThongTinTongThe.Rows.Add(row15);

                decimal laiGop = 0, laiGopBanBuon = 0, laiGopBanLe = 0;
                decimal doanhSo = 0, doanhSoBanBuon = 0, doanhSoBanLe = 0;
                decimal doanhSoBanBuonSach = 0, doanhSoBanBuonVPP = 0;
                decimal doanhSoTraLai = 0, doanhSoTraLaiSach = 0, doanhSoTraLaiVPP = 0;
                decimal laiTraLai = 0;

                #region tinh doanh số - lai gop trong ky

                #region Tính số tiền HangBanTraLai (tổng, bán buôn, bán lẻ)
                //Doanh số trả lại tổng
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoTraLai, SUM(Value7 - Value3) AS Lai \n" +
                                    "FROM KhoanMucChungTu  \n" +
                                    "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu   \n" +
                                    "					WHERE ChungTuGocID IN (SELECT ChungTuID  \n" +
                                    "											FROM ChungTu  \n" +
                                    "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu  \n" +
                                    "																	WHERE Ma IN (N'HangBanTraLai')  \n" +
                                    "																	)  \n" +
                                    "											AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi \n" +
                                    "											) \n" +
                                    "                  ) \n" +
                                    " AND Value5 IS NOT NULL AND Value3 IS NOT NULL \n";

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoTraLai"] != DBNull.Value)
                        {
                            doanhSoTraLai = (decimal)dr["DoanhSoTraLai"];
                        }
                        if (dr["Lai"] != DBNull.Value)
                        {
                            laiTraLai = (decimal)dr["Lai"];
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();

                //Doanh số trả lại SACH
                cmd.CommandText = "SELECT SUM(C.Value7) AS DoanhSoTraLaiSach  \n" +
                                    "FROM ChungTu A INNER JOIN LoaiChungTu LCT ON LCT.ID = A.LoaiChungTuID AND LCT.Ma IN (N'HangBanTraLai')  \n" +
                                    "												AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1   \n" +
                                    "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID  \n" +
                                    "	INNER JOIN KhoanMucChungTu C ON C.ChungTuID = B.ChungTuID  \n" +
                                    "	INNER JOIN VatTuHangHoa D ON D.ID = C.VatTuHangHoa1ID  \n" +
                                    "	INNER JOIN NhomVatTuHangHoa E ON E.ID = D.NhomVatTuHangHoaID   \n" +
                                    "WHERE (E.ID IN (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH') OR  \n" +
                                    "	E.NhomVatTuHangHoaChaID IN  (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH'))  \n" +
                                    "	AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL  \n";

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoTraLaiSach"] != DBNull.Value)
                        {
                            doanhSoTraLaiSach = (decimal)dr["DoanhSoTraLaiSach"];
                            //ucDigitalNumberDoanhSoBanLe.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["DoanhSoTraLaiSach"]);
                        }

                    }
                    catch
                    {
                    }
                }
                dr.Close();

                //Doanh số trả lại HANG KHAC
                cmd.CommandText = "SELECT SUM(C.Value7) AS DoanhSoTraLaiVPP  \n" +
                                    "FROM ChungTu A INNER JOIN LoaiChungTu LCT ON LCT.ID = A.LoaiChungTuID AND LCT.Ma IN (N'HangBanTraLai')  \n" +
                                    "												AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1   \n" +
                                    "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID  \n" +
                                    "	INNER JOIN KhoanMucChungTu C ON C.ChungTuID = B.ChungTuID  \n" +
                                    "	INNER JOIN VatTuHangHoa D ON D.ID = C.VatTuHangHoa1ID  \n" +
                                    "	INNER JOIN NhomVatTuHangHoa E ON E.ID = D.NhomVatTuHangHoaID   \n" +
                                   "WHERE (E.ID IN (SELECT ID FROM NhomVatTuHangHoa WHERE Ma != 'SACH') AND \n" +
                                     "	(E.NhomVatTuHangHoaChaID NOT IN  (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH') OR NhomVatTuHangHoaChaID IS NULL)) \n" +
                                     "	AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n";

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoTraLaiVPP"] != DBNull.Value)
                        {
                            doanhSoTraLaiVPP = (decimal)dr["DoanhSoTraLaiVPP"];
                            //ucDigitalNumberDoanhSoBanLe.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["DoanhSoTraLaiVPP"]);
                        }

                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                #region Tính lãi tổng
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBan, SUM(Value7 - Value3) AS Lai\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                        "																	) \n" +
                                        "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                        "											)\n" +
                                        "                   )\n" +
                                        " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["Lai"] != DBNull.Value)
                        {
                            laiGop = (decimal)dr["Lai"];
                            laiGop = laiGop - laiTraLai;
                            DataRow row = dtThongTinTongThe.NewRow();
                            row["Key"] = "LG";
                            row["Value"] = (decimal)dr["Lai"];
                            dtThongTinTongThe.Rows.Add(row);
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                #region Tách doanh số bán buôn và bán lẻ

                //bán buôn
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBanBuon, SUM(Value7 - Value3) AS LaiBanBuon\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang') \n" +
                                        "																	) \n" +
                                        "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                        "											)\n" +
                                        "                   )\n" +
                                        " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoBanBuon"] != DBNull.Value)
                        {
                            doanhSoBanBuon = (decimal)dr["DoanhSoBanBuon"];
                            doanhSoBanBuon = doanhSoBanBuon - doanhSoTraLai;
                        }
                        else
                        {
                            doanhSoBanBuon = doanhSoBanBuon - doanhSoTraLai;
                        }
                        DataRow row17 = dtThongTinTongThe.NewRow();
                        row17["Key"] = "DSBB";
                        row17["Value"] = doanhSoBanBuon;
                        dtThongTinTongThe.Rows.Add(row17);

                        if (dr["LaiBanBuon"] != DBNull.Value)
                        {
                            laiGopBanBuon = (decimal)dr["LaiBanBuon"];
                            laiGopBanBuon = laiGopBanBuon - laiTraLai;
                        }
                        else
                        {
                            laiGopBanBuon = laiGopBanBuon - laiTraLai;
                        }
                        DataRow row18 = dtThongTinTongThe.NewRow();
                        row17["Key"] = "LaiBB";
                        row17["Value"] = laiGopBanBuon;
                        dtThongTinTongThe.Rows.Add(row17);
                    }
                    catch
                    {
                    }
                }
                dr.Close();

                //bán lẻ
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBanLe,  SUM(Value7 - Value3) AS LaiBanLe\n" +
                                      "FROM KhoanMucChungTu \n" +
                                      "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                      "					WHERE GhiSo = 1 AND ChungTuGocID IN (SELECT ChungTuID \n" +
                                      "											FROM ChungTu \n" +
                                      "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                      "																	WHERE Ma IN (N'HoaDonBanLe') \n" +
                                      "																	) \n" +
                                      "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi \n" +
                                      "											)\n" +
                                      "                   )\n" +
                                      " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoBanLe"] != DBNull.Value)
                        {
                            doanhSoBanLe = (decimal)dr["DoanhSoBanLe"];
                            DataRow row18 = dtThongTinTongThe.NewRow();
                            row18["Key"] = "DSBL";
                            row18["Value"] = doanhSoBanLe;
                            dtThongTinTongThe.Rows.Add(row18);
                        }
                        if (dr["LaiBanLe"] != DBNull.Value)
                        {
                            laiGopBanLe = (decimal)dr["LaiBanLe"];
                            DataRow row17 = dtThongTinTongThe.NewRow();
                            row17["Key"] = "LaiBL";
                            row17["Value"] = laiGopBanLe;
                            dtThongTinTongThe.Rows.Add(row17);
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                #region Tách doanh số Sách và Hàng khác trong Bán buôn, Bán lẻ

                #region For SACH

                //Sách - bán buôn
                cmd.CommandText = "SELECT SUM(C.Value7) AS DoanhSoSach   --,  SUM(C.Value7 - C.Value3) AS LaiSach \n" +
                                     "FROM ChungTu A INNER JOIN LoaiChungTu LCT ON LCT.ID = A.LoaiChungTuID AND LCT.Ma IN (N'HoaDonBanHang') \n" +
                                     "												AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1  \n" +
                                     "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                     "	INNER JOIN KhoanMucChungTu C ON C.ChungTuID = B.ChungTuID \n" +
                                     "	INNER JOIN VatTuHangHoa D ON D.ID = C.VatTuHangHoa1ID \n" +
                                     "	INNER JOIN NhomVatTuHangHoa E ON E.ID = D.NhomVatTuHangHoaID  \n" +
                                     "WHERE (E.ID IN (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH') OR \n" +
                                     "	E.NhomVatTuHangHoaChaID IN  (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH')) \n" +
                                     "	AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoSach"] != DBNull.Value)
                        {
                            doanhSoBanBuonSach = (decimal)dr["DoanhSoSach"];
                            doanhSoBanBuonSach = doanhSoBanBuonSach - doanhSoTraLaiSach;
                        }
                        else
                        {
                            doanhSoBanBuonSach = doanhSoBanBuonSach - doanhSoTraLaiSach;
                        }
                        DataRow row31 = dtThongTinTongThe.NewRow();
                        row31["Key"] = "DSSBBuon";
                        row31["Value"] = doanhSoBanBuonSach;
                        dtThongTinTongThe.Rows.Add(row31);
                    }
                    catch
                    { }
                }
                dr.Close();

                //Sách - Bán Lẻ
                cmd.CommandText = "SELECT SUM(C.Value7) AS DoanhSoSach   --,  SUM(C.Value7 - C.Value3) AS LaiSach \n" +
                                     "FROM ChungTu A INNER JOIN LoaiChungTu LCT ON LCT.ID = A.LoaiChungTuID AND LCT.Ma IN (N'HoaDonBanLe') \n" +
                                     "												AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1  \n" +
                                     "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                     "	INNER JOIN KhoanMucChungTu C ON C.ChungTuID = B.ChungTuID \n" +
                                     "	INNER JOIN VatTuHangHoa D ON D.ID = C.VatTuHangHoa1ID \n" +
                                     "	INNER JOIN NhomVatTuHangHoa E ON E.ID = D.NhomVatTuHangHoaID  \n" +
                                     "WHERE (E.ID IN (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH') OR \n" +
                                     "	E.NhomVatTuHangHoaChaID IN  (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH')) \n" +
                                     "	AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoSach"] != DBNull.Value)
                        {
                            DataRow row31 = dtThongTinTongThe.NewRow();
                            row31["Key"] = "DSSBLe";
                            row31["Value"] = (decimal)dr["DoanhSoSach"];
                            dtThongTinTongThe.Rows.Add(row31);
                        }
                    }
                    catch
                    { }
                }
                dr.Close();

                #endregion

                #region For hàng khác
                //Hàng khác - Bán Buôn
                cmd.CommandText = "SELECT SUM(C.Value7) AS DoanhSoVPP --,  SUM(C.Value7 - C.Value3) AS LaiVPP \n" +
                                     "FROM ChungTu A INNER JOIN LoaiChungTu LCT ON LCT.ID = A.LoaiChungTuID AND LCT.Ma IN (N'HoaDonBanHang') \n" +
                                     "												AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1  \n" +
                                     "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                     "	INNER JOIN KhoanMucChungTu C ON C.ChungTuID = B.ChungTuID \n" +
                                     "	INNER JOIN VatTuHangHoa D ON D.ID = C.VatTuHangHoa1ID \n" +
                                     "	INNER JOIN NhomVatTuHangHoa E ON E.ID = D.NhomVatTuHangHoaID  \n" +
                                     "WHERE (E.ID IN (SELECT ID FROM NhomVatTuHangHoa WHERE Ma != 'SACH') AND \n" +
                                     "	(E.NhomVatTuHangHoaChaID NOT IN  (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH') OR NhomVatTuHangHoaChaID IS NULL)) \n" +
                                     "	AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoVPP"] != DBNull.Value)
                        {
                            doanhSoBanBuonVPP = (decimal)dr["DoanhSoVPP"];
                            doanhSoBanBuonVPP = doanhSoBanBuonVPP - doanhSoTraLaiVPP;
                        }
                        else
                        {
                            doanhSoBanBuonVPP = doanhSoBanBuonVPP - doanhSoTraLaiVPP;
                        }
                        DataRow row31 = dtThongTinTongThe.NewRow();
                        row31["Key"] = "DSVPPBBuon";
                        row31["Value"] = doanhSoBanBuonVPP;
                        dtThongTinTongThe.Rows.Add(row31);
                    }
                    catch
                    { }
                }
                dr.Close();

                //Hàng khác - Lẻ
                cmd.CommandText = "SELECT SUM(C.Value7) AS DoanhSoVPP --,  SUM(C.Value7 - C.Value3) AS LaiVPP \n" +
                                     "FROM ChungTu A INNER JOIN LoaiChungTu LCT ON LCT.ID = A.LoaiChungTuID AND LCT.Ma IN (N'HoaDonBanLe') \n" +
                                     "												AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1  \n" +
                                     "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                     "	INNER JOIN KhoanMucChungTu C ON C.ChungTuID = B.ChungTuID \n" +
                                     "	INNER JOIN VatTuHangHoa D ON D.ID = C.VatTuHangHoa1ID \n" +
                                     "	INNER JOIN NhomVatTuHangHoa E ON E.ID = D.NhomVatTuHangHoaID  \n" +
                                     "WHERE (E.ID IN (SELECT ID FROM NhomVatTuHangHoa WHERE Ma != 'SACH') AND \n" +
                                     "	(E.NhomVatTuHangHoaChaID NOT IN  (SELECT ID FROM NhomVatTuHangHoa WHERE Ma = 'SACH') OR NhomVatTuHangHoaChaID IS NULL)) \n" +
                                     "	AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoVPP"] != DBNull.Value)
                        {
                            DataRow row31 = dtThongTinTongThe.NewRow();
                            row31["Key"] = "DSVPPLe";
                            row31["Value"] = (decimal)dr["DoanhSoVPP"];
                            dtThongTinTongThe.Rows.Add(row31);
                        }
                    }
                    catch
                    { }
                }
                dr.Close();

                #endregion

                #endregion

                #endregion

                //lãi gộp trừ chi phí (lợi nhuận trước thuế)
                decimal tongChiPhi = chiPhiHoatDong + chiPhiSX + chiPhiKhac;
                decimal loiNhuanTruocThue = laiGop + thuKhac - tongChiPhi;
                DataRow row19 = dtThongTinTongThe.NewRow();
                row19["Key"] = "LTCP";
                row19["Value"] = loiNhuanTruocThue;
                dtThongTinTongThe.Rows.Add(row19);

                //Thuế thu nhập doanh nghiệp
                TaiKhoan tk821 = DanhMucTaiKhoan.Instance.GetBySoHieu("821");  // Thuế thu nhập doanh nghiệp
                TaiKhoan tk333 = DanhMucTaiKhoan.Instance.GetBySoHieu("333");  //THuế VAT
                SoDuTaiKhoan soDu821 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk821.ID);
                SoDuTaiKhoan soDu333 = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(cmd, thoiDiemDau, thoiDiemCuoi, tk333.ID);
                decimal phatSinh821 = Math.Abs(soDu821.DuNo - soDu821.DuCo);
                decimal phatSinh333 = Math.Abs(soDu333.DuNo - soDu333.DuCo);

                decimal tienThue = phatSinh821 + phatSinh333;

                DataRow row20 = dtThongTinTongThe.NewRow();
                row20["Key"] = "TTNGTGT";
                row20["Value"] = tienThue;
                dtThongTinTongThe.Rows.Add(row20);

                //Lãi lỗ thực (sau thuế)
                decimal laiLoThuc = loiNhuanTruocThue - tienThue;
                DataRow row21 = dtThongTinTongThe.NewRow();
                row21["Key"] = "LR";
                row21["Value"] = laiLoThuc;
                dtThongTinTongThe.Rows.Add(row21);

                //NguonVonKinhDoanh
                Guid taiKhoanNguonVonKinhDoanhID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("4111").ID;
                SoDuTaiKhoan soDuTaiKhoanNguonVonKinhDoanh = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanNguonVonKinhDoanhID);
                DataRow row22 = dtThongTinTongThe.NewRow();
                row22["Key"] = "NVKD";
                row22["Value"] = soDuTaiKhoanNguonVonKinhDoanh.DuNo - soDuTaiKhoanNguonVonKinhDoanh.DuCo;
                dtThongTinTongThe.Rows.Add(row22);

                //Convert to XML
                System.IO.StringWriter sw = new System.IO.StringWriter();
                XmlTextWriter xmltw = new XmlTextWriter(sw);
                dtThongTinTongThe.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                string xml = sw.ToString();
                return xml;
            }
            catch (Exception ex)
            {
                var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        DateTime IBIPortal.GetThoiDiemKhoaSoGanNhatOut(string dbName, DateTime thoiDiem)
        {
            try
            {
                ReUseLibs.ResetConnection(dbName);

                ThoiDiemKhoaSo thoiDiemKhoaSo = DanhMucThoiDiemKhoaSo.Instance.GetThoiDiemChotSoDuGanNhat(thoiDiem);
                return thoiDiemKhoaSo.ThoiDiem;

            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
        }

        string IBIPortal.DoanhSoLaiLoTheoTungChiNhanh(string dbName, DateTime thoiDiemDau, DateTime thoiDiemCuoi, int numInterval)
        {
            ReUseLibs.ResetConnection(dbName);
            //5 Ngày reindex 1 lần
            if (Config.ThoiDiemReIndexGanNhat is NullableDate)
            {
                TimeSpan span = DateTime.Now - (DateTime)Config.ThoiDiemReIndexGanNhat.Date;
                if (span.TotalDays > 5) ReUseLibs.ReIndexDB();
            }
            else
            {
                ReUseLibs.ReIndexDB();
                Config.ThoiDiemReIndexGanNhat = new NullableDate(DateTime.Now);
            }

            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                Controller.DsWCFBI ds = new DsWCFBI();
                DataTable dt = ReUseLibs.DoanhSoLaiLoTheoTungChiNhanh(cmd, dbName, thoiDiemDau, thoiDiemCuoi, numInterval);
                //Convert to XML
                System.IO.StringWriter sw = new System.IO.StringWriter();
                XmlTextWriter xmltw = new XmlTextWriter(sw);
                dt.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                string xml = sw.ToString();
                return xml;
            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        string IBIPortal.BienDongCongNoTienMatHangHoa(string dbName, DateTime thoiDiemDau, DateTime thoiDiemCuoi, int numInterval)
        {
            ReUseLibs.ResetConnection(dbName);
            //5 Ngày reindex 1 lần
            if (Config.ThoiDiemReIndexGanNhat is NullableDate)
            {
                TimeSpan span = DateTime.Now - (DateTime)Config.ThoiDiemReIndexGanNhat.Date;
                if (span.TotalDays > 5) ReUseLibs.ReIndexDB();
            }
            else
            {
                ReUseLibs.ReIndexDB();
                Config.ThoiDiemReIndexGanNhat = new NullableDate(DateTime.Now);
            }

            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);

            try
            {
                con.Open();
                DsWCFBI ds = new DsWCFBI();
                SqlCommand cmd = con.CreateCommand();
                DateTime dtPointNeedCalculate = thoiDiemDau;

                //Biến động tiền mặt
                TaiKhoan tk = DanhMucTaiKhoan.Instance.GetBySoHieu("1111");
                while (dtPointNeedCalculate <= thoiDiemCuoi)
                {
                    SoDuTaiKhoan soDu = Lists.SoDuTaiKhoan.CalculateSoDuSauBuTru(cmd, dtPointNeedCalculate, tk.ID);
                    DataRow row = ds.Tables["BienDongTienMat"].NewRow();
                    row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointNeedCalculate.Day, dtPointNeedCalculate.Month, dtPointNeedCalculate.Year);
                    row["Series1Value"] = Math.Abs(soDu.DuNo - soDu.DuCo) / 1000000;
                    ds.Tables["BienDongTienMat"].Rows.Add(row);

                    dtPointNeedCalculate = dtPointNeedCalculate.AddDays((double)numInterval);
                }

                //Biến động giá trị hàng hóa
                dtPointNeedCalculate = thoiDiemDau;
                tk = DanhMucTaiKhoan.Instance.GetBySoHieu("1561");
                while (dtPointNeedCalculate <= thoiDiemCuoi)
                {
                    SoDuTaiKhoan soDu = Lists.SoDuTaiKhoan.CalculateSoDuSauBuTru(cmd, dtPointNeedCalculate, tk.ID);
                    DataRow row = ds.Tables["BienDongHangHoa"].NewRow();
                    row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointNeedCalculate.Day, dtPointNeedCalculate.Month, dtPointNeedCalculate.Year);
                    row["Series1Value"] = Math.Abs(soDu.DuNo - soDu.DuCo) / 1000000;
                    ds.Tables["BienDongHangHoa"].Rows.Add(row);

                    dtPointNeedCalculate = dtPointNeedCalculate.AddDays((double)numInterval);
                }

                //Biến động Công nợ phải thu
                dtPointNeedCalculate = thoiDiemDau;
                tk = DanhMucTaiKhoan.Instance.GetBySoHieu("131");
                while (dtPointNeedCalculate <= thoiDiemCuoi)
                {
                    SoDuTaiKhoan soDu = Lists.SoDuTaiKhoan.CalculateSoDuSauBuTru(cmd, dtPointNeedCalculate, tk.ID);
                    DataRow row = ds.Tables["BienDongCongNoPhaiThu"].NewRow();
                    row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointNeedCalculate.Day, dtPointNeedCalculate.Month, dtPointNeedCalculate.Year);
                    row["Series1Value"] = Math.Abs(soDu.DuNo - soDu.DuCo) / 1000000;
                    ds.Tables["BienDongCongNoPhaiThu"].Rows.Add(row);

                    dtPointNeedCalculate = dtPointNeedCalculate.AddDays((double)numInterval);
                }

                //Biến động Công nợ phải trả
                dtPointNeedCalculate = thoiDiemDau;
                tk = DanhMucTaiKhoan.Instance.GetBySoHieu("331");
                while (dtPointNeedCalculate <= thoiDiemCuoi)
                {
                    SoDuTaiKhoan soDu = Lists.SoDuTaiKhoan.CalculateSoDuSauBuTru(cmd, dtPointNeedCalculate, tk.ID);
                    DataRow row = ds.Tables["BienDongCongNoPhaiTra"].NewRow();
                    row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointNeedCalculate.Day, dtPointNeedCalculate.Month, dtPointNeedCalculate.Year);
                    row["Series1Value"] = Math.Abs(soDu.DuNo - soDu.DuCo) / 1000000;
                    ds.Tables["BienDongCongNoPhaiTra"].Rows.Add(row);

                    dtPointNeedCalculate = dtPointNeedCalculate.AddDays((double)numInterval);
                }

                //Convert to XML
                System.IO.StringWriter sw = new System.IO.StringWriter();
                XmlTextWriter xmltw = new XmlTextWriter(sw);
                ds.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                string xml = sw.ToString();
                return xml;
                //return ds;
            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        string IBIPortal.DoanhSoLaiLoCacChiNhanh(string[] dbNames, DateTime thoiDiemDau, DateTime thoiDiemCuoi)
        {
            DsWCFBI ds = new DsWCFBI();

            //5 Ngày reindex 1 lần
            if (Config.ThoiDiemReIndexGanNhat is NullableDate)
            {
                TimeSpan span = DateTime.Now - (DateTime)Config.ThoiDiemReIndexGanNhat.Date;
                if (span.TotalDays > 5) ReUseLibs.ReIndexDB();
            }
            else
            {
                ReUseLibs.ReIndexDB();
                Config.ThoiDiemReIndexGanNhat = new NullableDate(DateTime.Now);
            }
            SqlConnection con = null;
            try
            {
                foreach (string db in dbNames)
                {
                    ReUseLibs.ResetConnection(db);
                    con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
                    try
                    {
                        con.Open();
                        SqlCommand cmd = con.CreateCommand();

                        DsWCFBI.DoanhSoLaiLoCacChiNhanhRow row = ds.DoanhSoLaiLoCacChiNhanh.NewDoanhSoLaiLoCacChiNhanhRow();
                        row = ReUseLibs.DoanhSoLaiLoTheoTungChiNhanh(cmd, row, db, thoiDiemDau, thoiDiemCuoi);

                        if (!ds.DoanhSoLaiLoCacChiNhanh.Contains(row))
                            ds.DoanhSoLaiLoCacChiNhanh.AddDoanhSoLaiLoCacChiNhanhRow(row);
                    }
                    catch (Exception ex)
                    {
                        //var error = new CustomFaultMsg { Message = ex.Message };
                        throw new FaultException(ex.Message);
                    }
                    finally
                    {
                        con.Close();
                    }
                }

                //Convert to XML
                System.IO.StringWriter sw = new System.IO.StringWriter();
                XmlTextWriter xmltw = new XmlTextWriter(sw);
                ds.DoanhSoLaiLoCacChiNhanh.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                string xml = sw.ToString();
                return xml;
            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
            finally
            {
                if (con != null && con.State == ConnectionState.Open)
                    con.Close();
            }
        }

        string IBIPortal.UserLogin(string dbName, string userName, string password)
        {
            ReUseLibs.ResetConnection(dbName);
            DsWCFBI ds = new DsWCFBI();
            try
            {
                User user = User.CurUser = User.Login(userName, password);
                if (user == null)
                    return "";
                else
                {
                    DataRow row = ds.User.NewRow();
                    row["ID"] = user.ID;
                    row["UserName"] = user.UserName;
                    row["Password"] = user.Password;
                    ds.User.Rows.Add(row);

                    //Convert to XML
                    System.IO.StringWriter sw = new System.IO.StringWriter();
                    XmlTextWriter xmltw = new XmlTextWriter(sw);
                    ds.User.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                    string xml = sw.ToString();
                    return xml;
                }

            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
            finally
            {
            }
        }

        string IBIPortal.GetDataBaseList(string dbName, string userName)
        {
            ReUseLibs.ResetConnection(dbName);
            DsWCFBI ds = new DsWCFBI();

            if (userName != "SupAdmin")
            {
                User user = UserList.Instance.GetByUserName(userName);

                try
                {
                    string filterCondition = "ID IN (";
                    DanhMucUser_Database dmUser_DB = DanhMucUser_Database.GetByUserID(user.ID);
                    foreach (User_Database item in dmUser_DB)
                    {
                        //filterCondition += string.Format("'{0}', ", item.DataBaseID);
                        filterCondition += "CONVERT('{" + item.DataBaseID + "}','System.Guid'), ";
                    }
                    if (filterCondition.EndsWith(", "))
                        filterCondition = filterCondition.Substring(0, filterCondition.Length - 2) + ")";

                    DataTable dt = DanhMucDatabaseList.Filter(filterCondition).DataView.ToTable();

                    //Convert to XML
                    System.IO.StringWriter sw = new System.IO.StringWriter();
                    XmlTextWriter xmltw = new XmlTextWriter(sw);
                    dt.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                    string xml = sw.ToString();
                    return xml;
                }
                catch (Exception ex)
                {
                    //var error = new CustomFaultMsg { Message = ex.Message };
                    throw new FaultException(ex.Message);
                }
                finally
                {
                }
            }
            else  //Nếu là SupAdmin thì return Full
            {
                DataTable dt = DanhMucDatabaseList.Instance.DataTable;

                //Convert to XML
                System.IO.StringWriter sw = new System.IO.StringWriter();
                XmlTextWriter xmltw = new XmlTextWriter(sw);
                dt.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                string xml = sw.ToString();
                return xml;
            }
        }

        string IBIPortal.BienDongDoanhSoBanHang(string dbName)
        {
            ReUseLibs.ResetConnection(dbName);
            DataSet ds = new DataSet();

            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT N'Doanh so' Name, convert(datetime, '1/' + CAST(DATEPART(MONTH, A.Ngay) as nchar(2)) + '/' + CAST(DATEPART(year, A.Ngay) as nchar(4)), 103) Date,  SUM(A.SotienNT) Amount \n" +
                                  "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                  "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                  "GROUP BY convert(datetime, '1/' + CAST(DATEPART(MONTH, A.Ngay) as nchar(2)) + '/' + CAST(DATEPART(year, A.Ngay) as nchar(4)), 103)  \n";

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt1 = new DataTable();
                dt1.TableName = "BienDongDoanhSo";
                da.Fill(dt1);

                cmd.CommandText = "SELECT N'Lai lo' Name, convert(datetime, '1/' + CAST(DATEPART(MONTH, A.Ngay) as nchar(2)) + '/' + CAST(DATEPART(year, A.Ngay) as nchar(4)), 103) Date, SUM(KM.Value7 - KM.Value2) Amount \n" +
                                 "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                 "				INNER JOIN KhoanMucChungTu KM ON KM.ChungTuID = B.ChungTuID  \n" +
                                 "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                  "GROUP BY convert(datetime, '1/' + CAST(DATEPART(MONTH, A.Ngay) as nchar(2)) + '/' + CAST(DATEPART(year, A.Ngay) as nchar(4)), 103)  \n";
                DataTable dt2 = new DataTable();
                da.Fill(dt2);
                dt2.TableName = "BienDongLaiLo";
                ds.Tables.Add(dt1);
                ds.Tables.Add(dt2);

                //Convert to XML
                System.IO.StringWriter sw = new System.IO.StringWriter();
                XmlTextWriter xmltw = new XmlTextWriter(sw);
                ds.WriteXml(xmltw, XmlWriteMode.IgnoreSchema);
                string xml = sw.ToString();
                return xml;
            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        List<decimal[]> IBIPortal.TyLeLoiNhuanSoVoiTongVonDauTu(string[] dbName, DateTime thoiDiemDau, DateTime thoiDiemCuoi)
        {
            List<decimal[]> result = new List<decimal[]>();
            try
            {
                foreach (string strCon in dbName)
                {
                    ReUseLibs.ResetConnection(strCon);

                    decimal[] child = ReUseLibs.LoiNhuanVonVaDauTuTungChiNhanh(LocalSettings.ConnectString, thoiDiemDau, thoiDiemCuoi);
                    result.Add(child);
                }
                return result;
            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }

        }
    }
}

